package treballfinalUF2;
import java.sql.*;
public class Connexio  implements java.io.Serializable {

		private static final long serialVersionUID = 1L;		
		private Connection miConexion = null;

		public Connexio() {

		}

		/***/


		public Connection getMiConexion() {
			return miConexion;
		}

		public void setMiConexion(Connection miConexion) {
			this.miConexion = miConexion;
		}

		public void creaBBDD() throws SQLException{
			
				Statement stmt = null;
				
				stmt = getMiConexion().createStatement();
				
		        stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS miBaseUF2FinalJSP");
		        
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS miBaseUF2FinalJSP.empresa (idempresa MEDIUMINT NOT NULL AUTO_INCREMENT,"
						+ "nomempresa varchar(300) NOT NULL,"
						+ "password varchar(300) NOT NULL,"
						+ "mail varchar(300) NOT NULL,"
						+ "descripcio varchar(300) NOT NULL,"
						+ "visible BOOLEAN NOT NULL,"
						+ "PRIMARY KEY (idempresa));");
				
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS miBaseUF2FinalJSP.alumne (idalumne MEDIUMINT NOT NULL AUTO_INCREMENT,"
						+ "nomalumne varchar(30) NOT NULL,"
						+ "password varchar(30) NOT NULL,"
						+ "mail varchar(300) NOT NULL,"
						+ "visible BOOLEAN NOT NULL,"
						+ "PRIMARY KEY (idalumne));");
				
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS miBaseUF2FinalJSP.oferta (idoferta MEDIUMINT NOT NULL AUTO_INCREMENT,"
						+ "idempresa MEDIUMINT NOT NULL,"
						+ "descripcio varchar(300) NOT NULL,"
						+ "fechainici TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,"
						+ "fechafi TIMESTAMP,"
						+ "PRIMARY KEY (idoferta));");
				
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS miBaseUF2FinalJSP.ofertaempresaalumne"
						+ "(idoferta MEDIUMINT NOT NULL ,"
						+ "idempresa MEDIUMINT NOT NULL,"
						+ "idalumne MEDIUMINT NOT NULL,"						
						+ "PRIMARY KEY (idoferta, idempresa, idalumne),"						
						+"FOREIGN KEY (idoferta) REFERENCES oferta(idoferta)" 
						+ " ON DELETE CASCADE ON UPDATE CASCADE) ; ");
				
				
		}


		//Establiment de la connexio
		public Connection establishConnection (){		
			try {
				Class.forName("com.mysql.jdbc.Driver");

				String BaseDeDatos = "jdbc:mysql://localhost:3306/";

				setMiConexion(DriverManager.getConnection(BaseDeDatos, "root", "admin") );

				if(getMiConexion() != null){
					System.out.println("Conexion Exitosa!");
				}else{
					System.out.println("Conexion Fallida!");               
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

			return miConexion;
		}

		//tanquem la connexio
		public void closeMiConnexion() {
			try {
				this.miConexion.close();
				System.out.println("Conexion cerrada con éxito!");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}


		}
		
		
		

	}
